﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;

namespace WindowsFormsApplication1
{
    public partial class frmDanhmucSV : Form
    {

        bool temp;       //tùy chọn insert or update
        int count;      //Số lượng sinh viên
        int index;      //ví trí của sinh viên đó trong dữ liệu
        string madantoc;    //lấy mã dân tộc khi tùy chọn lku_madantoc --> update or insert dữ liệu
        string matongiao;   //lấy mã tôn giáo khi tùy chọn lku_Matongiao -->update or insert dữ liệu
        string malop;   //lấy mã tôn giáo khi tùy chọn lku_Malop -->update or insert dữ liệu
        string Thi_TT;  //lấy hình thức thi or tuyển thẳng khi tùy chọn cbThi_TT
        string phai;
        bool xoa;
        private static SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=SM;Integrated Security=True");
        string sql = "select * from T01_Sinh_vien sv, T03_Lop_hoc lh, T07_Dan_toc dt, T08_Ton_giao tg where dt.MadantocID = sv.MadantocID and sv.MatongiaoID = tg.MatongiaoID and lh.MalopID = sv.MalopID";
        public frmDanhmucSV()
        {
            InitializeComponent();
            //lookup Dân tộc
            string get_lkuDantoc = "select * from T07_Dan_toc";
            DataSet ds1 = FillDataset(get_lkuDantoc);
            lkuDantoc.Properties.DataSource = ds1.Tables[0];
            lkuDantoc.Properties.DisplayMember = "Tendantoc";
            lkuDantoc.Properties.ValueMember = "MadantocID";
            //lookup Tôn giáo
            string get_lkuTongiao = "Select * from T08_Ton_giao";
            DataSet ds2 = FillDataset(get_lkuTongiao);
            lkuTongiao.Properties.DataSource = ds2.Tables[0];
            lkuTongiao.Properties.DisplayMember = "Tentongiao";
            lkuTongiao.Properties.ValueMember = "MatongiaoID";
            //lookup Lớp học
            string get_lkuLophoc = "Select * from T03_Lop_hoc";
            DataSet ds3 = FillDataset(get_lkuLophoc);
            lkuMalop.Properties.DataSource = ds3.Tables[0];
            lkuMalop.Properties.DisplayMember = "MalopID";
            lkuMalop.Properties.ValueMember = "MalopID";

            //combobox();
            disable_textbox();
        }

        //public void combobox()
        //{
        //    cbThi.Reset();
        //    DataSet ds4 = FillDataset("Select Thi_TT from T01_Sinh_vien group by Thi_TT");
        //    List<string> thi_tt = new List<string>();
        //    foreach (DataRow row in ds4.Tables[0].Rows)
        //        thi_tt.Add(row["Thi_TT"].ToString());
        //    foreach (string thi in thi_tt)
        //        cbThi.Properties.Items.Add(thi);
        //    ds4.Tables[0].Reset();
        //}

        DataSet FillDataset(string sql)
        {
            DataSet ds = new DataSet();
            try
            {
                SqlDataAdapter adap = new SqlDataAdapter(sql, connection);
                adap.Fill(ds);
                adap.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error" + ex.Message);
            }
            return ds;
        }

        public void get_gridviewdanhsachsv()
        {
            DataSet ds = FillDataset("select * from T01_Sinh_vien");
            gCol_MaSV.FieldName = "MasvID";
            gCol_Holot.FieldName = "Holot";
            gCol_Ten.FieldName = "Ten";
            gCol_Noisinh.FieldName = "Noisinh";
            gCol_Diachi.FieldName = "Diachi";
            gridControl1.DataSource = ds.Tables[0];
        }

        public void get_Tabdanhsachsv(string sql, DataRow row)
        {
            reset_lookupedit();
            matongiao = row["MatongiaoID"].ToString();
            madantoc = row["MadantocID"].ToString();
            txtMSSV.Text = row["MasvID"].ToString();
            txtHolot.Text = row["Holot"].ToString();
            txtTen.Text = row["Ten"].ToString();
            //lkuMalop.Properties.NullText = row["MalopID"].ToString();
            lkuMalop.Text = row["MalopID"].ToString();
            lkuMalop.ClosePopup();
            string temp = row["Ngaysinh"].ToString();
            if (temp == "")
                dNgaysinh.EditValue = "";
            else
            {
                DateTime ngaysinh = DateTime.Parse(temp);
                dNgaysinh.EditValue = ngaysinh.ToShortDateString();
            }
            txtNoiSinh.Text = row["Noisinh"].ToString();
            phai = row["Phai"].ToString();
            if (phai == "True")
                rgPhai.SelectedIndex = 0;
            else
                rgPhai.SelectedIndex = 1;
            txtDiachi.Text = row["Diachi"].ToString();
            txtDienthoai.Text = row["Dienthoai"].ToString();
            txtEmail.Text = row["Email"].ToString();
            txtNamTNPT.Text = row["NamTNPT"].ToString();
            cbThi.Text = row["Thi_TT"].ToString(); 
           // lkuDantoc.Properties.NullText = row["Tendantoc"].ToString();
            lkuDantoc.Text = row["Tendantoc"].ToString();
            lkuDantoc.ClosePopup();
            //lkuTongiao.Properties.NullText = row["Tentongiao"].ToString();
            lkuTongiao.Text = row["Tentongiao"].ToString();
            lkuTongiao.ClosePopup();
            txtQuoctich.Text = row["Quoctich"].ToString();
            txtHotencha.Text = row["Hotencha"].ToString();
            txtNghenghiepcha.Text = row["Nghenghiepcha"].ToString();
            txtHotenme.Text = row["Hotenme"].ToString();
            txtNghenghiepme.Text = row["Nghenghiepme"].ToString();
            mGhichu.Text = row["Ghichu"].ToString();
            matongiao = row["MatongiaoID"].ToString();
            madantoc = row["MadantocID"].ToString();
            if (row["Hinh"] != System.DBNull.Value)
            //if(row["Hinh"]
            {
                byte[] MyData = (byte[])row["Hinh"];
                MemoryStream ms = new MemoryStream(MyData);
                picHinh3x4.Image = Image.FromStream(ms);

            }
            else
                picHinh3x4.Image = null;
        }

        public void reset_lookupedit()
        {
            lkuMalop.Refresh();
            lkuDantoc.Refresh();
            lkuTongiao.Refresh();
        }

        private void frmDanhmucSV_Load(object sender, EventArgs e)
        {
            
            get_gridviewdanhsachsv();            
            DataSet ds = FillDataset(sql);
            DataRow row = ds.Tables[0].Rows[0];
            count = ds.Tables[0].Rows.Count;
            get_Tabdanhsachsv(sql, row);
        }
        private void gridView1_CustomRowCellEditForEditing(object sender, DevExpress.XtraGrid.Views.Grid.CustomRowCellEditEventArgs e)
        {
            //string masv = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, gCol_MaSV).ToString();
            //string str = "select * from T01_Sinh_vien sv, T03_Lop_hoc lh, T07_Dan_toc dt, T08_Ton_giao tg where dt.MadantocID = sv.MadantocID and sv.MatongiaoID = tg.MatongiaoID and lh.MalopID = sv.MalopID and sv.MasvID = N'" + masv + "'";
            //DataSet ds = FillDataset(str);
            //DataRow row = ds.Tables[0].Rows[0];
            //int[] i = gridView1.GetSelectedRows();
            //index = i[0];
            //get_Tabdanhsachsv(str,row);
        }


        private void btnVedau_Click(object sender, EventArgs e)
        {
            btnLuu.Enabled = false;
            btnBoqua.Enabled = false;            
            DataSet ds = FillDataset(sql);
            DataRow row = ds.Tables[0].Rows[0];
            get_Tabdanhsachsv(sql, row);
            index = ds.Tables[0].Rows.IndexOf(row);
            
        }

        private void btnLui_Click(object sender, EventArgs e)
        {
            btnLuu.Enabled = false;
            btnBoqua.Enabled = false;
            index--;            
            if (index >= 0)
            {
                DataSet ds = FillDataset(sql);
                DataRow row = ds.Tables[0].Rows[index];
                get_Tabdanhsachsv(sql, row);
                index = ds.Tables[0].Rows.IndexOf(row);
            }
            else
            {
                index++;
                if (xoa == false)
                    MessageBox.Show("Bạn đang ở mẫu tin đầu tiên");
                else
                    btnVedau_Click(sender, e);
            }
        }

        private void bntToi_Click(object sender, EventArgs e)
        {
            btnLuu.Enabled = false;
            btnBoqua.Enabled = false;
            index++;            
            if (index < count)
            {
                DataSet ds = FillDataset(sql);
                DataRow row = ds.Tables[0].Rows[index];
                get_Tabdanhsachsv(sql, row);
                index = ds.Tables[0].Rows.IndexOf(row);
            }
            else
            {
                index--;
                MessageBox.Show("Bạn đang ở mẫu tin cuối cùng");
            }
        }

        private void btnVecuoi_Click(object sender, EventArgs e)
        {
            btnLuu.Enabled = false;
            btnBoqua.Enabled = false;            
            DataSet ds = FillDataset(sql);
            DataRow row = ds.Tables[0].Rows[count - 1];
            get_Tabdanhsachsv(sql, row);
            index = ds.Tables[0].Rows.IndexOf(row);
        }

        private void btnThem_Click(object sender, EventArgs e)
        {
            btnVedau.Enabled = false;
            btnVecuoi.Enabled = false;
            btnLui.Enabled = false;
            btnToi.Enabled = false;
            btnThem.Enabled = false;
            btnSua.Enabled = false;
            btnXoa.Enabled = false;
            btnThoat.Enabled = false;
            btnLuu.Enabled = true;
            btnBoqua.Enabled = true;
            picHinh3x4.Enabled = true;
            temp = false;
            txtMSSV.Text = "";
            txtHolot.Text = "";
            txtTen.Text = "";
            lkuMalop.EditValue = "";
            txtNoiSinh.Text = "";
            dNgaysinh.EditValue = "";
            txtDiachi.Text = "";
            txtDienthoai.Text = "";
            txtEmail.Text = "";
            txtNamTNPT.Text = "";
            cbThi.Text = "";
            lkuDantoc.EditValue = "";
            lkuTongiao.EditValue = "";
            txtQuoctich.Text = "";
            txtHotencha.Text = "";
            txtNghenghiepcha.Text = "";
            txtHotenme.Text = "";
            txtNghenghiepme.Text = "";
            mGhichu.Text = "";
            picHinh3x4.Image = null;
            enable_textbox();
        }

        public void query(string command)
        {
            if (connection.State == ConnectionState.Open)
                connection.Close();
            connection.Open();
            SqlCommand sqlCommand = connection.CreateCommand();
            sqlCommand.CommandText = command;
            SqlDataReader dr = sqlCommand.ExecuteReader();
            connection.Close();
        }

        private void btnXoa_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Bạn có chắc muốn xóa không?.", "Quản lý sinh viên", MessageBoxButtons.OKCancel, MessageBoxIcon.None) == DialogResult.OK)
            {
                try
                {
                    xoa = true;
                    string str = "delete from T01_Sinh_Vien where MasvID=N'" + txtMSSV.Text + "' ";
                    query(str);
                    get_gridviewdanhsachsv();
                    btnLui_Click(sender, e);
                }
                catch (Exception err)
                {
                    MessageBox.Show("Error: " + err.Message);
                }
            }
        }

        private void btnSua_Click(object sender, EventArgs e)
        {
            temp = true;
            btnVedau.Enabled = false;
            btnVecuoi.Enabled = false;
            btnLui.Enabled = false;
            btnToi.Enabled = false;
            btnThem.Enabled = false;
            btnSua.Enabled = false;
            btnXoa.Enabled = false;
            btnThoat.Enabled = false;
            btnLuu.Enabled = true;
            btnBoqua.Enabled = true;
            picHinh3x4.Enabled = true;
            enable_textbox();
        }

        private void btnLuu_Click(object sender, EventArgs e)
        {
            bool ktra = dxValidationProvider1.Validate();
            if (ktra)
            {
                if (temp == false)
                {
                    string sql = "select * from  T01_Sinh_vien sv, T03_Lop_hoc lh where sv.MalopID = lh.MalopID and sv.MalopID = '" + lkuMalop.Text + "'";
                    DataSet ds = FillDataset(sql);
                    int i = ds.Tables[0].Rows.Count;
                    DataRow row = ds.Tables[0].Rows[i - 1];
                    string a = row["MasvID"].ToString();
                    int j = Int32.Parse(a.Substring(a.Length - 3));
                    j++;
                    string tam = null;
                    if (j < 10)
                    {
                        tam = lkuMalop.Text + "00" + j.ToString();
                    }
                    if (j > 10 && j < 100)
                    {
                        tam = lkuMalop.Text + "0" + j.ToString();
                    }
                    string str = "insert into T01_Sinh_vien (MasvID, MalopID, Holot, Ten, Ngaysinh, Noisinh, Phai, Diachi, Dienthoai, Email, NamTNPT, Thi_TT, MadantocID, MatongiaoID, Quoctich, Hotencha, Nghenghiepcha, Hotenme, Nghenghiepme, Ghichu) values (N'" + tam + "', N'" + lkuMalop.Text + "', N'" + txtHolot.Text + "', N'" + txtTen.Text + "', N'" + dNgaysinh.EditValue + "', N'" + txtNoiSinh.Text + "', N'" + phai + "',  N'" + txtDiachi.Text + "', N'" + txtDienthoai.Text + "', N'" + txtEmail.Text + "', N'" + txtNamTNPT.Text + "', N'" + cbThi.Text + "', N'" + madantoc + "', N'" + matongiao + "', N'" + txtQuoctich.Text + "', N'" + txtHotencha.Text + "', N'" + txtNghenghiepcha.Text + "', N'" + txtHotenme.Text + "', N'" + txtNghenghiepme.Text + "', N'" + mGhichu.Text + "')";
                    query(str);
                    if (picture != null)
                    {
                        string Update = "Update T01_Sinh_vien set Hinh = @HinhAnh where MasvID = '" + tam + "'";
                        updateImage(Update, picture, "HinhAnh");
                        picture = null;
                    }
                }
                else
                {
                    reset_lookupedit();
                    string str = "update T01_Sinh_Vien set MalopID = N'" + lkuMalop.Text + "', Holot = N'" + txtHolot.Text + "', Ten = N'" + txtTen.Text + "', Ngaysinh = N'" + dNgaysinh.EditValue + "', Noisinh = N'" + txtNoiSinh.Text + "', Phai = N'" + phai + "', Diachi = N'" + txtDiachi.Text + "', Dienthoai = N'" + txtDienthoai.Text + "', Email = N'" + txtEmail.Text + "', NamTNPT = N'" + txtNamTNPT.Text + "', Thi_TT = N'" + cbThi.Text + "', MadantocID = N'" + madantoc + "', MatongiaoID = N'" + matongiao + "', Quoctich = N'" + txtQuoctich.Text + "', Hotencha = N'" + txtHotencha.Text + "', Nghenghiepcha = N'" + txtNghenghiepcha.Text + "', Hotenme = N'" + txtHotenme.Text + "', Nghenghiepme = N'" + txtNghenghiepme.Text + "', Ghichu = N'" + mGhichu.Text + "' where MasvID = N'" + txtMSSV.Text + "'";
                    query(str);
                    if (picture != null)
                    {
                        string Update = "Update T01_Sinh_vien set Hinh = @HinhAnh where MasvID = '" + txtMSSV.Text + "'";
                        updateImage(Update, picture, "HinhAnh");
                        picture = null;
                    }
                }
                
                //combobox();
                disable_textbox();
                btnVedau.Enabled = true;
                btnVecuoi.Enabled = true;
                btnLui.Enabled = true;
                btnToi.Enabled = true;
                btnThem.Enabled = true;
                btnSua.Enabled = true;
                btnXoa.Enabled = true;
                btnThoat.Enabled = true;
                btnLuu.Enabled = false;
                btnBoqua.Enabled = false;
                get_gridviewdanhsachsv();
            }
            else
                MessageBox.Show("Bạn chưa nhập dữ liệu cần thiết", "Quản lý sinh viên", MessageBoxButtons.OK, MessageBoxIcon.None);
       
        }

        private void btnBoqua_Click(object sender, EventArgs e)
        {
            btnVedau.Enabled = true;
            btnVecuoi.Enabled = true;
            btnLui.Enabled = true;
            btnToi.Enabled = true;
            btnThem.Enabled = true;
            btnSua.Enabled = true;
            btnXoa.Enabled = true;
            btnThoat.Enabled = true;
            btnLuu.Enabled = false;
            btnBoqua.Enabled = false;
            picHinh3x4.Image = null;
            picHinh3x4.Enabled = false;
            reset_lookupedit();
            DataSet ds = FillDataset(sql);
            DataRow row = ds.Tables[0].Rows[index];
            get_Tabdanhsachsv(sql, row);
            disable_textbox();
        }

        private void btnThoat_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void lkuDantoc_EditValueChanged(object sender, EventArgs e)
        {
            madantoc = lkuDantoc.EditValue.ToString();
        }

        private void lkuTongiao_EditValueChanged(object sender, EventArgs e)
        {
            matongiao = lkuTongiao.EditValue.ToString();
        }

        private void lkuMalop_EditValueChanged(object sender, EventArgs e)
        {
            malop = lkuMalop.EditValue.ToString();
        }

        private void rgPhai_SelectedIndexChanged(object sender, EventArgs e)
        {
            int selectedIndex = rgPhai.SelectedIndex;
            if (selectedIndex == 0)
                phai = "true";
            else
                phai = "false";
        }

        private void cbThi_SelectedIndexChanged(object sender, EventArgs e)
        {
            Thi_TT = cbThi.SelectedText.ToString();
        }

        public void disable_textbox()
        {
            picHinh3x4.Enabled = false;
            txtMSSV.Enabled = false;
            txtHolot.Enabled = false;
            txtTen.Enabled = false;
            lkuMalop.Enabled = false;            
            dNgaysinh.Enabled = false;                        
            txtNoiSinh.Enabled = false;            
            rgPhai.Enabled = false;            
            txtDiachi.Enabled = false;            
            txtDienthoai.Enabled = false;            
            txtEmail.Enabled = false;            
            txtNamTNPT.Enabled = false;            
            cbThi.Enabled = false;            
            lkuDantoc.Enabled = false;            
            lkuTongiao.Enabled = false;            
            txtQuoctich.Enabled = false;            
            txtHotencha.Enabled = false;            
            txtNghenghiepcha.Enabled = false;            
            txtHotenme.Enabled = false;            
            txtNghenghiepme.Enabled = false;
            mGhichu.Enabled = false;            
        }

        public void enable_textbox()
        {
            txtHolot.Enabled = true;
            txtTen.Enabled = true;
            lkuMalop.Enabled = true;
            dNgaysinh.Enabled = true;
            txtNoiSinh.Enabled = true;
            rgPhai.Enabled = true;
            txtDiachi.Enabled = true;
            txtDienthoai.Enabled = true;
            txtEmail.Enabled = true;
            txtNamTNPT.Enabled = true;
            cbThi.Enabled = true;
            lkuDantoc.Enabled = true;
            lkuTongiao.Enabled = true;
            txtQuoctich.Enabled = true;
            txtHotencha.Enabled = true;
            txtNghenghiepcha.Enabled = true;
            txtHotenme.Enabled = true;
            txtNghenghiepme.Enabled = true;
            mGhichu.Enabled = true;
        }

        private void gridView1_RowClick(object sender, DevExpress.XtraGrid.Views.Grid.RowClickEventArgs e)
        {
            string masv = gridView1.GetRowCellValue(e.RowHandle, gCol_MaSV).ToString();
            string str = "select * from T01_Sinh_vien sv, T03_Lop_hoc lh, T07_Dan_toc dt, T08_Ton_giao tg where dt.MadantocID = sv.MadantocID and sv.MatongiaoID = tg.MatongiaoID and lh.MalopID = sv.MalopID and sv.MasvID = N'" + masv + "'";
            DataSet ds = FillDataset(str);
            DataRow row = ds.Tables[0].Rows[0];
            int[] i = gridView1.GetSelectedRows();
            index = i[0];
            get_Tabdanhsachsv(str, row);
        }

        //up hinh
        #region
        private byte[] picture = null;

        public static int updateImage(string str, byte[] val, string image)
        {
            if (connection.State == ConnectionState.Closed)
                connection.Open();
            SqlCommand cmd = new SqlCommand(str, connection);
            cmd.Parameters.Add(image, SqlDbType.Image);
            cmd.Parameters[0].Value = val;
            return cmd.ExecuteNonQuery();
        }
        private void picHinh3x4_Click(object sender, EventArgs e)
        {
            opd.FileName = "";
            opd.Filter = ("JPEG files (*jpg;*jpeg)|*jpg;*jpeg|Bitmap files (*bmp;*.dib)|*bmp;*.dib|PNG files (*png)|*png");
            if (opd.ShowDialog() == DialogResult.OK)
            {
                if (opd.FileName != null)
                {
                    FileStream fs = new FileStream(opd.FileName, FileMode.Open);
                    picture = new byte[fs.Length];
                    fs.Read(picture, 0, (int)fs.Length);
                    picHinh3x4.Image = Image.FromStream(fs);
                    fs.Close();
                }
            }
        }
        #endregion

    }
}
